package cn.jsj.hzu.qm.facecloud.ciuser.dao.impl;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import cn.jsj.hzu.qm.facecloud.ciuser.dao.VCiciDao;
import cn.jsj.hzu.qm.facecloud.ciuser.vo.VCici;
import cn.jsj.hzu.qm.facecloud.utils.VCici_Sort;

/**
 * 只读
 * */
public class VCiciDaoImpl implements VCiciDao {
	JdbcTemplate jdbcTemplate = null;
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	
	@Override

	public int getAllVCiciNum() {
		
		String sqlString = "SELECT COUNT(*) FROM v_cici";
		
		int i = jdbcTemplate.queryForObject(sqlString, Integer.class);
		return i;
	}
	
	@Override
	public int getAllVCiciNum(int labid) {
		String sqlString = "SELECT COUNT(*) FROM v_cici WHERE labid=?";
		
		int i = jdbcTemplate.queryForObject(sqlString, Integer.class, new Object[] {labid});
		return i;
	}
	
	@Override
	public List<VCici> getAllVCicis() {
		
		String sqlString = "SELECT * FROM v_cici";
		
		List<VCici> vCicis = jdbcTemplate.query(sqlString, new BeanPropertyRowMapper<>(VCici.class));
		return vCicis;
	}
	
	
	@Override
	public List<VCici> getAllVCicisByLimit(int start, int length) {
		
		String sqlString = "SELECT * FROM v_cici ORDER BY ciin DESC, id LIMIT ?,?";
		
		List<VCici> vCicis = jdbcTemplate.query(sqlString, new BeanPropertyRowMapper<>(VCici.class), new Object[] {start, length});
		
		
		return vCicis;
	}
	
	/**
	 * 计算分页
	 * */
	private int [] getLimitByPage(int page, int perpage, int sum) {
		int [] res = new int[2];
		
		int start = (page - 1) * perpage;
		int length = perpage;
		
		if (start >= sum) {
			return null;
		}

		if (start + perpage >= sum) {
			length = sum-start;
		}
		
		res[0] = start;
		res[1] = length;
		
		return res;
	}
	
	@Override
	public List<VCici> getAllVCicisByPage(int page, int perpage, int sum) {
		int [] res = getLimitByPage(page, perpage, sum);
		int start = res[0];
		int length = res[1];
		
		return getAllVCicisByLimit(start, length);
	}
	
	
	
	public List<VCici> getAllVCicisByLimitAndLabid(int start, int length, int labid) {
		
		// 【先where 再order by 再limit】
		//   https://blog.csdn.net/weixin_34235371/article/details/86007473
		String sqlString = "SELECT * FROM v_cici WHERE labid=? ORDER BY ciin DESC, id LIMIT ?,? ";
		
		List<VCici> vCicis = jdbcTemplate.query(sqlString, new BeanPropertyRowMapper<>(VCici.class), new Object[] {labid, start, length});
		
		
		return vCicis;
	}
	
	@Override
	public List<VCici> getAllVCicisByPageAndLabid(int page, int perpage, int sum, int labid) {
		int [] res = getLimitByPage(page, perpage, sum);
		int start = res[0];
		int length = res[1];
		
		return getAllVCicisByLimitAndLabid(start, length, labid);
	}
	
	/**
	 * @deprecated
	 * */
	@Override
	public List<VCici> getVCicisByFuzzzyAndLimit(VCici likeVCici, int start, int length){
		String sqlString = "SELECT * FROM v_cici WHERE labname LIKE ? AND cinname LIKE ? AND userid LIKE ? LIMIT ?,?";
		String useridString;
		if (likeVCici.getUserid() == -1) {
			useridString = "";
		}
		else {
			useridString = "" + likeVCici.getUserid();
		}
		
		
		List<VCici> vCicis = jdbcTemplate.query(sqlString, new BeanPropertyRowMapper<>(VCici.class), new Object[] {likeVCici.getLabname(), likeVCici.getCiname(), likeVCici.getUserid(), start, length});
		
		// 排序！
		if (vCicis != null) {
			VCici_Sort.sort(vCicis);
		}
		return vCicis;
	}
	
	@Override
	public List<VCici> getVCicisByFuzzzyAndLimitByPage(VCici likeVCici, int page, int perpage, int sum){
		int [] res = getLimitByPage(page, perpage, sum);
		int start = res[0];
		int length = res[1];
		return getVCicisByFuzzzyAndLimit(likeVCici, start, length);
	}
	
	
	/**
	 * @deprecated
	 * */
	@Override
	public List<VCici> getVCicisByFuzzzyAndLimitWithLabid(VCici likeVCici,int labid, int start, int length){
		
		if (likeVCici.getCiname() == null || likeVCici.getCiname().equals("")) {
			likeVCici.setCiname("%");
		}
		
		String sqlString = "SELECT * FROM v_cici WHERE labid=?  AND cinname LIKE ?  LIMIT ?,?";

		
		List<VCici> vCicis = jdbcTemplate.query(sqlString, new BeanPropertyRowMapper<>(VCici.class), new Object[] {labid, likeVCici.getCiname(), start, length});
		
		// 排序！
		if (vCicis != null) {
			VCici_Sort.sort(vCicis);
		}
		return vCicis;
	}
	
	/**
	 * @deprecated
	 * */
	@Override
	public List<VCici> getVCicisByFuzzzyAndLimitWithLabidByPage(VCici likeVCici,int labid, int page, int perpage, int sum){
		int [] res = getLimitByPage(page, perpage, sum);
		int start = res[0];
		int length = res[1];
		return getVCicisByFuzzzyAndLimitWithLabid(likeVCici,labid, start, length);
	}
	
	
	

	
}
